package com.yabushan.web.utils.excelUtils;

/**
 * @Author yabushan
 * @Date 2021/1/15 18:30
 * @Version 1.0
 */

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;

import javax.imageio.ImageIO;
import javax.xml.bind.DatatypeConverter;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * 导出Excel-含图片
 * @author Runge
 *
 */
public class ExcelImage {
    public static void main(String[] args) {
        new ExcelImage().creatExportFile("D:\\upload\\", "表格.xls");
    }


    public boolean creatExportFile(String path, String name) {
        List<LinkedHashMap<String, String>> list = new ArrayList<LinkedHashMap<String,String>>();
        LinkedHashMap param = new LinkedHashMap();
        param.put("USER_NAME","sxf");
        param.put("PICTURE","logo.jpg");
        list.add(param);
        if(null != list && list.size()>0){
            //模板路径
            String templatePath = path + "exportAlarm.xlsx";
            //获取工作表

            HSSFWorkbook workbook = getWorkbook(templatePath);
            //chuangsheet页
            HSSFSheet sheet = workbook.getSheetAt(0);
            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            // 写入sheet数据
            try {
                writeSheet(workbook, sheet, list, patriarch);
            } catch (IOException e) {
                e.printStackTrace();
            }  // 正常
            // 导出Excel文件
            exportExcel(path, name, workbook);
            return true;
        }else{
            return false;
        }
    }

    /**
     * 得到工作表
     * @param filePath
     * @return
     */
    public HSSFWorkbook getWorkbook(String filePath){
        HSSFWorkbook xssfWorkbook = null;
        FileInputStream fis = null;
        try {
            //创建Excel文件的输入流对象
            fis = new FileInputStream(filePath);
            xssfWorkbook = new HSSFWorkbook(fis);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //使用完成后关闭流
            try {
                if (fis != null)
                    fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return xssfWorkbook;
    }
    /*
     * 得到单元格内容
     */
    public static HSSFCell getCell(HSSFRow row, int colIndex){
        HSSFCell cell = row.getCell(colIndex);
        if (cell == null) {
            cell = row.createCell(colIndex);
            cell.setCellValue("");
        }
        return cell;
    }
    /*
     * 导出Excel
     */
    public void exportExcel(String path, String fileName, HSSFWorkbook wb){
        OutputStream os = null;
        try {
            // 输出的文件流保存到本地文件
            File tempFile = new File(path);
            if (!tempFile.exists()) {
                tempFile.mkdirs();
            }
            os = new FileOutputStream(path + File.separator + fileName);
            wb.write(os);
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            try {
                if (os != null)
                    os.close();
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 写入sheet数据
     * @param workbook
     * @param sheet
     * @param list
     * @throws IOException
     */
    public void writeSheet(HSSFWorkbook workbook, HSSFSheet sheet, List<LinkedHashMap<String, String>> list, HSSFPatriarch patriarch) throws IOException{
        int rowIndex = 0;
        HSSFRow row = null;//行
        HSSFCell cell = null;//列
        ByteArrayOutputStream byteArrayOut = null;//字符输出对象
        String baseArr[];//存放格式化base64Str后的数组 0-图片格式；1-base64字符
        String type;//图片格式
        BufferedImage buffer_Img;
        HSSFClientAnchor anchor;
        for(LinkedHashMap<String, String> r : list){
            rowIndex++;
            row = sheet.getRow(rowIndex);
            if(row == null)row = sheet.createRow(rowIndex);
            // 序号
            cell = getCell(row, 0);
            cell.setCellValue(rowIndex);
            // 姓名
            cell = getCell(row, 9);
            cell.setCellValue(r.get("USER_NAME"));
            // 监控告警图片
            if(StringUtils.isNotBlank(r.get("PICTURE"))){
                row.setHeight((short)2400);// 设置行高
                byteArrayOut = new ByteArrayOutputStream();
                // 格式化，取得图片类型和base64字符串码
                baseArr = base64SplitTypeAndStr(r.get("PICTURE"));
                type = baseArr[0];
                // base64转BufferedImage
                buffer_Img = base64ToBufferedImage(baseArr[1]);
                ImageIO.write(buffer_Img, type, byteArrayOut);// 写入
                //图片位置
                anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)10, rowIndex, (short)11, rowIndex+1);
                // 插入图片
                patriarch.createPicture((HSSFClientAnchor)anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            }else{
                row.setHeight((short)360);// 设置行高
            }
        }
    }





    /**
     * base64字符串，分隔图片类型和base64串码
     * @param base64
     * @return [0]-type; [1]-base64Str
     */
    public static String [] base64SplitTypeAndStr(String base64){
        String [] retArr = {"", ""};
        if(StringUtils.isNotBlank(base64)){
            //先取出前缀
            String typeStr = base64.split(",")[0];
            //去掉前缀后的串码
            retArr[1] = base64.replaceFirst(typeStr+",", "");
            //取出图片类型
            retArr[0] = typeStr.replace("data:image/", "").replace(";base64", "");
        }
        return retArr;
    }

    /**
     * base64 编码转换为 BufferedImage
     * @param base64
     * @return
     */
    public static BufferedImage base64ToBufferedImage(String base64) {
        try {
            byte[] bytes1 = DatatypeConverter.parseBase64Binary(base64);
            ByteArrayInputStream bais = new ByteArrayInputStream(bytes1);
            return ImageIO.read(bais);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

}



